package web_hr;
import java.sql.*;
import java.util.*;

/**
 * 连接数据库类
 * 
 * 封装简单的sql语句，方便查询
 * @author Intel
 *
 */
public class Model {
	private Connection conn = null;
	private Statement stmt = null;
	private String table_name = null;
	private int colCount = 0;
	Vector heads = new Vector();
	
	public Model(String table_name){
		this.table_name = table_name;
		this.getCol();
	}


	/**************************
	 * DateBase Open & Close
	 *
	 *************************/
    public Statement open(){
        try{
        	/*****************************
        	 *  connect to databases  使用前请导入相应的驱动包
        	 *****************************/

            //mysql
//          Class.forName("org.gjt.mm.mysql.Driver");
//        	String url ="jdbc:mysql://localhost:3306/test";
//        	String user = "root";
//        	String password = "";
//        	conn = DriverManager.getConnection(url,user,password);
        	//oracle
          Class.forName("oracle.jdbc.driver.OracleDriver");
          String url = "jdbc:oracle:thin:@localhost:1521:xe";
          String user = "hr";
          String password = "123456";
          conn = null;
          conn = DriverManager.getConnection(url,user,password);
          stmt = null;
          stmt = conn.createStatement();
        }catch(Exception e){
            e.printStackTrace();
        } 
        return stmt;
    }
    
	public  void close(){
		if(this.stmt != null){   // 关闭声明   
	        try{   
	            this.stmt.close() ;   
	        }catch(SQLException e){   
	            e.printStackTrace() ;   
	        }   
	     }   
         if(this.conn != null){  // 关闭连接对象   
	         try{   
	            this.conn.close() ;   
	         }catch(SQLException e){   
	            e.printStackTrace() ;   
	         }  
         }
	} 
    
    public int getColConunt(){
    	return this.colCount;
    }
	
	
    /**
     * 获取表中列名
     */
    public String[] getCol(){
    	this.open();
    	String[] col = null ;
    	String str = "select * from "+this.table_name;
    	ResultSet rs = null;
		try {
			rs = this.stmt.executeQuery(str);
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println(e);
		}
    	ResultSetMetaData rsmd = null;
		try {
			rsmd = rs.getMetaData();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	colCount  = 0;
		try {
			colCount = rsmd.getColumnCount();
			col = new String[colCount];

	    	for(int i=1;i<=colCount;i++){
	    		try {
					heads.add(rsmd.getColumnName(i));
					col[i-1] = rsmd.getColumnName(i).toString();
				} catch (SQLException e) {
					e.printStackTrace();
				}
	    	}
		} catch (SQLException e) {
			e.printStackTrace();	
		}finally{
			this.close();
		}
    	return col;
    }

		
	/**
	 * 运行sql语句的方法
	 * 
	 * @param s：sql语句
	 * @return 结果集
	 */
	public ResultSet eQuery(String s){
		ResultSet rs = null;
		this.open();
		try {
			rs = this.stmt.executeQuery(s);
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println(e);
		}
		return rs;
	}
	
	/**
	 * add(Map<String,String> mymap)
	 * @param Map
	 * @return int 受影响的记录数
	 */
	public  int add(Map<String,String> mymap){
		StringBuffer key = new StringBuffer();
		StringBuffer value = new StringBuffer();
		for(String keys:mymap.keySet()){
			key = key.append(keys+" ");
			value = value.append("'"+mymap.get(keys)+"' ");
		}
		String key1 = key.toString().replace(" ",",");
		 key1 = key1.substring(0,key1.length()-1);
		String value1 = value.toString().replace(" ",",");
		value1 = value1.substring(0,value1.length()-1);
		String sql = "insert into "+this.table_name+"("+key1+") values ("+value1+")";
		return this.eUpdate(sql);
	}
	
	/**
	 * find(String key,String value)
	 * @param String key,String value
	 * @return Object[]
	 */
	public Object[] find(String key,String value){
		this.open();
		Object[] s = new Object[50];
		try{
			String str = "select * from "+this.table_name+" where "+key+" = '"+value+"'";
//			System.out.println(str);
			ResultSet rs = this.eQuery(str);
			while(rs.next()){
				for(int i=1;i<=this.colCount;i++){
					s[i-1] = rs.getObject(i);
				}
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return s;
	}
	
	
	
	public ArrayList executeQuery(String s){
		ArrayList l = new ArrayList();
		ResultSet rs = null;
		ResultSetMetaData rsmd = null;
		Map m = null;
		int count = 0;
		this.open();
		try {
			rs = this.stmt.executeQuery(s);
			rsmd = rs.getMetaData();
			count = rsmd.getColumnCount();	
			while(rs.next()){
	    	for(int i=1;i<=count;i++){
	    		try {
					//heads.add(rsmd.getColumnName(i));
	    			m = new LinkedHashMap();
	    			m.put(rsmd.getColumnName(i),rs.getObject(rsmd.getColumnName(i)));
	    			System.out.println(m.get(rsmd.getColumnName(i)));
				} catch (SQLException e) {
					e.printStackTrace();
				}
	    	}
	    	l.add(m);
		}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}

//		Iterator ite = l.iterator();
//		while(ite.hasNext()){
//			ite.next().
//		}
		return l;
	}
	
	/**
	 * 执行更新语句
	 * @param sql语句
	 * @return 受影响的记录数
	 */
	public int eUpdate(String s){
		this.open();
		int row = 0;
		try {
			row = this.stmt.executeUpdate(s);
			this.conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.close();
		}
		return row;
	}
	
	
	/**
	 * 遍历id是否存在
	 * 
	 * @param：表明，id
	 */
	public boolean idIsExist(long id){
		String tableId = null;
		boolean flag = false;
		String s = "select * from "+this.table_name+" where "+ tableId +"="+id;
		ResultSet rs = this.eQuery(s);
		try {
			if(rs.next()){
				flag = true;
			}else{
				flag = false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.close();
		}
		return flag;
	}
	
	/**
	 * 遍历某个值是否存在
	 * 
	 * @param：列名，记录名
	 * @return：boolean
	 */
	public boolean isExist(String col,String name){
		boolean flag = false;
		String s = "select * from "+this.table_name+" where "+ col +"="+ "'"+name+"'";
		ResultSet rs = this.eQuery(s);
		try {
			if(rs.next()){
				flag = true;
			}else{
				flag = false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.close();
		}
		return flag;
	}
	
	public boolean delete(int id){
		String str = "delete from "+this.table_name+" where id = '"+id+"'";
		int i = this.eUpdate(str);
		if(i==1){
			return true;
		}else{
			return false;
		}
	}
	
	//col是id列的名称
	public boolean delete(String col,int id){
		String str = "delete from "+this.table_name+" where "+col+" = '"+id+"'";
		int i = this.eUpdate(str);
		if(i==1){
			return true;
		}else{
			return false;
		}
	}
	
	
	
	public static void main(String[] args){
		Model m = new Model("hr_employee");
		
//		ResultSet rs = m.eQuery("select * from hr_employee");
//		try {
//			while(rs.next()){
//				System.out.println(rs.getString(2));
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		
		//add()
//		Map<String, String> map = new HashMap<String, String>();
//		  map.put("job_id", "123");
//		  map.put("job_title", "value2");
//		m.add(map);
		
		//getCol()
//		String[] v = m.getCol();
//		for(String str:v){
//			System.out.println(str);
//		}
		
		//System.out.println(m.delete("employee_id1",1010));
//		List ll = m.executeQuery("select name from students ");
//		Iterator ite = ll.iterator();
//		while(ite.hasNext()){
//			ite.next()
//		}
		
		//find()
//		Object[] mm =m.find("job_id", "123");
//		for(Object obj:mm){
//			System.out.println(obj);
//		}
	
	}
}

